<?php

/**
 * Adapter Mysqli
 *
 * @author     luigi.pastore (gigi@la-fabbrica.org)
 * @package    LaFabbrica
 * @copyright  Copyright (c) 2011 Luigi Pastore
 * 
 * @author luigi.pastore
 * @created  15-apr-2011
 */
class Classes_Core_MysqliLayer
{

    /**
     * La variabile statica privata che conterrà l'istanza univoca
     * della classe.
     */
    private static $_instance = null;

    /**
     * Constructor
     */
    function __construct()
    {
	# Conteggio Query...
	$this->query_count = 0;

	/**
	 * Connessione Db
	 */
	$db_config = parse_ini_file( APPLICATION_PATH .'/config/db.ini', true );

	if( false === ( $this->link = new mysqli( $db_config[APPLICATION_ENV]['host'], $db_config[APPLICATION_ENV]['user'], $db_config[APPLICATION_ENV]['pass'], $db_config[APPLICATION_ENV]['dbname'] ) ) )
	    throw new Exception( 'Errore DB : ' . $this->link->connect_error );

	# Settiamo Charset...
	$this->link->set_charset( 'utf8' );

    }

    /**
     * Begin a transaction.
     *
     * @return void
     */
    public function _beginTransaction()
    {
        $this->link->autocommit(false);
    }

    /**
     * Commit a transaction.
     *
     * @return void
     */
    public function _commit()
    {
        $this->link->commit();
        $this->link->autocommit(true);
    }

    /**
     * Roll-back a transaction.
     *
     * @return void
     */
    public function _rollBack()
    {
        $this->link->rollback();
        $this->link->autocommit(true);
    }    
    
    /**
     * Singleton
     *
     */
    public static function getInstance()
    {
	if( null === self::$_instance )
	{
	    self::$_instance = new self();
	}

	return self::$_instance;
    }

    /**
     * Esegue Query...
     *
     */
    public function query( $sql )
    {
	$this->query_count += 1;
	$query =  $this->link->query( $sql );
	
//	if( APPLICATION_ENV == 'development' )
//            echo '<script type="text/javascript">console.log("'. addslashes( $sql ) .'")</script>';
//            var_dump( $sql );

	if( !$query )
	    throw new Classes_Core_Exceptions( $this->link->error );
	
	return $query;
    }

    /**
     * Esegue Fetch...
     *
     */
    public function fetch( $query_id )
    {
	return mysqli_fetch_assoc( $query_id );
    }

    /**
     * Esegue Query Multiple...
     *
     */
    public function multiQuery( $sql )
    {
	$this->link->multi_query( $sql );

	# Restituisce mysqli_result
	return $this->link;
    }

    /**
     * @todo: ELIMINARE. Usare selectQuery()
     * SELECT SEMPLICE
     * $data = array(
     * 		- FROM	-	[0] => 'nome_tabella',
     * 		- CAMPI -	[1] => 'campo' OPPURE array( 'campo 1', 'campo 2', ... ),
     * 		- WHERE -	[2] => array( 'campo' => array( 0 => 'valore 1', 1 => 'valore 2', ... )
     *					  OPPURE array( 'campo 1' => 'valore 1', 'campo 2' => 'valore 2', ...)
     * 	 - LEFT JOIN -  [3] => array( 'tabella1' => array( 'tabella1.campo1', 'tabella2.campo2' ), 'tabella2' => ...;
     *
     */
    public function simpleSelectQuery( $data )
    {
	# Campi
	if( is_array( $data[1] ) AND count( $data[1] ) )
	{
	    $fields = implode( ', ', array_values( $data[1] ) );
	}
	else
	{
	    $fields = $data[1];
	}

	# Left Join
	if( is_array( $data[3] ) AND count( $data[3] ) )
	{
	    foreach( $data[3] as $join_table => $join_fields )
	    {
		$join .= ' LEFT JOIN ' . $join_table . ' ON ' . implode( ' = ', array_values( $join_fields ) );
	    }
	}

	# Where
	if( is_array( $data[2] ) )
	{
	    foreach( $data[2] as $field => $values )
	    {
		if( is_array( $values ) AND count( $values ) )
		{
		    $where_tmp[] = $field . ' IN (\'' . implode( '\', \'', array_values( array_map( array( 'self', 'clean' ), $values ) ) ) . '\')';
		}
		else
		{
		    $where_tmp[] = $field . ' = \'' . $this->clean( $data[2][$field] ) . '\'';
		}
	    }

	    $where = implode( ' AND ', $where_tmp );
	}

	$this->sql = 'SELECT ' . $fields . ' FROM ' . $data[0] . '' . ( $join ? $join : '' ) . ( $where ? ' WHERE ' . $where : '' );

	//var_dump( $this->sql ); 
        //exit;
	return $this->query( $this->sql );

    }

    /**
     * SELECT
     * $data = array(
     * 		- FROM	-	['from'] => 'nome_tabella',
     * 		- CAMPI -	['fields'] => 'campo' OPPURE array( 'campo 1', 'campo 2', ... ),
     * 		- WHERE -	['where'] => array( 'campo' =>
     * 									  array( 0 => 'valore 1', 1 => 'valore 2', ... )
     * 						   OPPURE array( 'campo 1' => 'valore 1', 'campo 2' => 'valore 2', ...)
     *                                             OPPURE new Classes_SqlExpression ('espressione sql');
     * 	 - LEFT JOIN -  ['join'] => array( 'tabella1' => array( 'tabella1.campo1', 'tabella2.campo2' ), 'tabella2' => ...;
     *   - ORDER BY  -  ['orderby'] => array( 'campo1' => 'ordine1', 'campo2' => 'ordine2' ),
     *   - LIMIT  	 -  ['limit'] => array( 'start', 'end' )
     *
     */
    public function selectQuery( $data )
    {
	# Campi
	if( is_array( $data['fields'] ) AND count( $data['fields'] ) )
	{
	    $fields = implode( ', ', array_values( $data['fields'] ) );
	}
	else
	{
	    $fields = $data['fields'];
	}

	# Left Join
	if( is_array( $data['join'] ) AND count( $data['join'] ) )
	{
	    foreach( $data['join'] as $join_table => $join_fields )
	    {
		$join .= ' LEFT JOIN ' . $join_table . ' ON ' . implode( ' = ', array_values( $join_fields ) );
	    }
	}

	# Where
	if( is_array( $data['where'] ) AND count( $data['where'] ) )
	{
	    foreach( $data['where'] as $field => $values )
	    {
		if( is_array( $values ) AND count( $values ) )
		{
		    $where_tmp[] = $field . ' IN (\'' . implode( '\', \'', array_values( array_map( array( 'self', 'clean' ), $values ) ) ) . '\')';
		}
		else
		{
		    $where_tmp[] = $field . ' = \'' . $this->clean( $data['where'][$field] ) . '\'';
		}
	    }

            $where = implode( ' AND ', $where_tmp );
	}
        elseif( $data['where'] instanceof Classes_SqlExpression )
        {
            //$where = $this->clean( $data['where'] );
            $where = $data['where'];
        }

	# Order By
	if( is_array( $data['orderby'] ) )
	{
            foreach( $data['orderby'] AS $field => $sort )
                $orderby .= $field .' '. $sort .', ';
            
            $orderby = substr( $orderby, 0, -2 );
	}

	# Limit
	if( is_array( $data['limit'] ) AND count( $data['limit'] ) == 2 )
	{
	    $limit = $data['limit'][0] . ', ' . $data['limit'][1];
	}

	$this->sql = 'SELECT ' . $fields . ' FROM ' . $data['from'] . '' . ( $join ? $join : '' ) . ( $where ? ' WHERE ' . $where : '' ) . ( $orderby ? ' ORDER BY ' . $orderby : '' ) . ( $limit ? ' LIMIT ' . $limit : '' );
        
//	var_dump( $this->sql );
//	exit;

	return $this->query( $this->sql );
    }

    /**
     * INSERT
     * $data = array(
     * 					[0] => 'nome_tabella',
     * 					[1] => array( 'campo 1' => 'valore 1',
     * 						          'campo 2' => 'valore 2', ... ),
     *
     */
    public function insertQuery( $data )
    {
	$fields = array_keys( $data[1] );
	$values = array_values( array_map( array( 'self', 'clean' ), $data[1] ) );

	$sql = 'INSERT INTO ' . $data[0] . ' (' . implode( ', ', $fields ) . ') VALUES ( \'' . implode( "','", $values ) . '\')';

//	var_dump($sql); exit;
	return $this->query( $sql ) ? $this->link->insert_id : $this->link->error;
    }

    /**
     * UPDATE
     * $data = array(
     * 				[0] => 'nome_tabella',
     * 		- SET -		[1] => 'campo = valore' OPPURE array( 'campo 1' => 'valore 1',
     * 														   'campo 2' => 'valore 2', ... ),
     * 		- WHERE -	[2] => 'campo = valore' OPPURE array( 'campo' =>
     * 														  array( 0 => 'valore 1', 1 => 'valore 2', ... )
     */
    public function updateQuery( $data )
    {
	# Campi
	if( is_array( $data[1] ) AND count( $data[1] ) )
	{
	    foreach( $data[1] as $field => $val )
                if( $val instanceof Classes_SqlExpression )
                    $fields[] = $field . ' = ' . $this->clean( $val ) . '';
                else
                    $fields[] = $field . ' = \'' . $this->clean( $val ) . '\'';

	    $fields = implode( ', ', $fields );
	}

	# Where
	$field = key( $data[2] );

	if( is_array( $data[2][$field] ) AND count( $data[2][$field] ) )
	{
	    foreach( $data[2][$field] as $val )
		$where[] = $field . ' = ' . $val;

	    $where = implode( ' AND ', $where );
	}
	else
	{
	    $where = $field . ' = ' . $data[2][$field];
	}

	$sql = 'UPDATE ' . $data[0] . ' SET ' . $fields . ' WHERE ' . $where;

//	var_dump( $sql );exit();

	return $this->query( $sql ) ? $this->link->affected_rows : $this->link->error;
    }

    /**
     * DELETE
     * $data = array(
     * 				[0] => 'nome_tabella',
     * 		- WHERE -	[1] => 'campo = valore' OPPURE array( 'campo' =>
     * 										array( 0 => 'valore 1', 1 => 'valore 2', ... )
     * 	  - CONDITION - [2] => 'AND' OPPURE 'OR'
     */
    public function deleteQuery( $data )
    {
	if( is_array( $data[1] ) )
	{
	    foreach( $data[1] as $field => $values )
	    {
		if( is_array( $values ) AND count( $values ) )
		{
		    $where_tmp[] = $field . ' IN (\'' . implode( '\', \'', array_values( array_map( array( 'self', 'clean' ), $values ) ) ) . '\')';
		}
		else
		{
		    $where_tmp[] = $field . ' = \'' . $this->clean( $data[1][$field] ) . '\'';
		}
	    }

	    $data[2] = ($data[2] == 'AND') ? ' AND ' : ' OR ';
	    $where = implode( $data[2], $where_tmp );
	}

	$sql = 'DELETE FROM ' . $data[0] . '' . ( $where ? ' WHERE ' . $where : '' );

//	var_dump( $sql );exit();

	return $this->query( $sql ) ? $this->link->affected_rows : $this->link->error;
    }

    /**
     * REPLACE
     * $data = array(
     * 					[0] => 'nome_tabella',
     * 					[1] => array( 
     *						[0] => array( 'campo 1' => 'valore 1',
     *								'campo 2' => 'valore 2', ... ),
     *						[1] => array( 'campo 1' => 'valore 1',
     *								'campo 2' => 'valore 2', ... ),
     *
     */    
    public function replaceQuery( $data )
    {
	# Campi
	if( is_array( $data[1] ) AND count( $data[1] ) )
	{
	    $fields = array_keys($data[1][0]);
	}

	# Valori
	if( is_array( $data[1] ) AND count( $data[1] ) )
	{
	    foreach( $data[1] AS $val )
	    {
		$values[] = implode( "','",array_values( array_map( array( 'self', 'clean' ), $val ) ));
	    }
	}
	$sql = 'REPLACE INTO ' . $data[0] . '(' . implode( ', ', $fields ) . ') VALUES(\'' . implode( '\'),(\'', $values ) . '\')';
//var_dump($sql); exit;

	return $this->query( $sql ) ? $this->link->affected_rows : $this->link->error;
    }


    public function error()
    {
	return $this->link->error;
    }

    private function clean( $str )
    {
	return $this->link->real_escape_string( trim( $str ) );
    }

}
